﻿
Create PROCEDURE dbo.SP_MembersModulesDTO_Select
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @Birthdate DateTime   =  NULL 
, @ConfessionFather NChar (10)  =  NULL 
, @CountryId Int   =  NULL 
, @CreationDate DateTime   =  NULL 
, @DisplayName NVarChar (256)  =  NULL 
, @Email NVarChar (256)  =  NULL 
, @FirstName NVarChar (256)  =  NULL 
, @Gender TinyInt   =  NULL 
, @ImagePath NVarChar (50)  =  NULL 
, @IsActive Bit   =  NULL 
, @LastName NVarChar (256)  =  NULL 
, @LastUpdateDate DateTime   =  NULL 
, @MemberId Int   =  NULL 
, @NationalityId Int   =  NULL 
, @Password NVarChar (500)  =  NULL 
,@ModuleName NvarChar (256) = null
	AS
			WITH CTE_Members AS
			( 
			SELECT dbo.Members.MemberId, dbo.Members.FirstName, dbo.Members.LastName,
			 dbo.Members.DisplayName, dbo.Members.Email, dbo.Members.Password, 
                   dbo.Members.Gender, dbo.Members.Birthdate, 
                   dbo.Members.NationalityId, dbo.Members.CountryId, dbo.Members.CreationDate, dbo.Members.IsActive, 
                         dbo.Members.ImagePath, dbo.Members.LastUpdateDate,
                          dbo.Members.ConfessionFather, dbo.Modules.ModuleName, 
                          dbo.Modules.ModuleId
,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
							WHEN @OrderColume='Birthdate' THEN CONVERT(varchar(50), Birthdate)
						    WHEN @OrderColume='ConfessionFather' THEN ConfessionFather
							WHEN @OrderColume='CountryId' THEN CONVERT(varchar(50), CountryId)
							WHEN @OrderColume='CreationDate' THEN CONVERT(varchar(50), CreationDate)
						 	WHEN @OrderColume='DisplayName' THEN DisplayName
						    WHEN @OrderColume='Email' THEN Email
						 	WHEN @OrderColume='FirstName' THEN FirstName
							WHEN @OrderColume='Gender' THEN CONVERT(varchar(50), Gender)
						    WHEN @OrderColume='ImagePath' THEN ImagePath
							WHEN @OrderColume='IsActive' THEN CONVERT(varchar(50), IsActive)
						 	WHEN @OrderColume='LastName' THEN LastName
						    WHEN @OrderColume='LastUpdateDate' THEN CONVERT(varchar(50), LastUpdateDate)
							WHEN @OrderColume='MemberId' THEN CONVERT(varchar(50), dbo.Members.MemberId)
							WHEN @OrderColume='NationalityId' THEN CONVERT(varchar(50), NationalityId)
						 	WHEN @OrderColume='Password' THEN Password
							WHEN @OrderColume='ModuleName' THEN ModuleName
							ELSE 
							CONVERT(varchar(50), dbo.Members.MemberId)
					        	END
			) AS "RowNumber"
  			FROM   dbo.MembersInModules INNER JOIN
                   dbo.Members ON dbo.MembersInModules.MemberId = dbo.Members.MemberId INNER JOIN
                   dbo.Modules ON dbo.MembersInModules.ModuleId = dbo.Modules.ModuleId 
			where 
								(Birthdate=@Birthdate OR @Birthdate Is Null)
										AND(ConfessionFather=@ConfessionFather OR @ConfessionFather Is Null)
										AND(CountryId=@CountryId OR @CountryId Is Null)
										AND(CreationDate=@CreationDate OR @CreationDate Is Null)
										AND(DisplayName=@DisplayName OR @DisplayName Is Null)
										AND(Email=@Email OR @Email Is Null)
										AND(FirstName=@FirstName OR @FirstName Is Null)
										AND(Gender=@Gender OR @Gender Is Null)
										AND(ImagePath=@ImagePath OR @ImagePath Is Null)
										AND(IsActive=@IsActive OR @IsActive Is Null)
										AND(LastName=@LastName OR @LastName Is Null)
										AND(LastUpdateDate=@LastUpdateDate OR @LastUpdateDate Is Null)
										AND(dbo.Members.MemberId=@MemberId OR @MemberId Is Null)
										AND(NationalityId=@NationalityId OR @NationalityId Is Null)
										AND(Password=@Password OR @Password Is Null)
									    AND(ModuleName=@ModuleName OR @ModuleName Is Null)
								)
			SELECT  MemberId, FirstName,LastName, DisplayName,Email,Password, 
             Gender, Birthdate, NationalityId, CountryId, 
             CreationDate, IsActive, ImagePath, LastUpdateDate,
             ConfessionFather, ModuleId ,ModuleName 
                         
			FROM CTE_Members
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (
			SELECT Count(dbo.Members.MemberId)AS TotalRecords 
            FROM  dbo.MembersInModules INNER JOIN
                   dbo.Members ON dbo.MembersInModules.MemberId = dbo.Members.MemberId INNER JOIN
                   dbo.Modules ON dbo.MembersInModules.ModuleId = dbo.Modules.ModuleId 
			
            where 
								       (Birthdate=@Birthdate OR @Birthdate Is Null)
										AND(ConfessionFather=@ConfessionFather OR @ConfessionFather Is Null)
										AND(CountryId=@CountryId OR @CountryId Is Null)
										AND(CreationDate=@CreationDate OR @CreationDate Is Null)
										AND(DisplayName=@DisplayName OR @DisplayName Is Null)
										AND(Email=@Email OR @Email Is Null)
										AND(FirstName=@FirstName OR @FirstName Is Null)
										AND(Gender=@Gender OR @Gender Is Null)
										AND(ImagePath=@ImagePath OR @ImagePath Is Null)
										AND(IsActive=@IsActive OR @IsActive Is Null)
										AND(LastName=@LastName OR @LastName Is Null)
										AND(LastUpdateDate=@LastUpdateDate OR @LastUpdateDate Is Null)
										AND(dbo.Members.MemberId=@MemberId OR @MemberId Is Null)
										AND(NationalityId=@NationalityId OR @NationalityId Is Null)
										AND(Password=@Password OR @Password Is Null)
										AND(ModuleName=@ModuleName OR @ModuleName Is Null)
									
					 	
			)